package cn.itlaobing.eshop.dao;

import cn.itlaobing.eshop.entity.GoodsDTO;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * Created by Administrator on 2017/11/14.
 */
public class GoodsDao extends BaseDao{
    public List<GoodsDTO> queryHotGoods()throws SQLException{
        QueryRunner queryRunner=new QueryRunner(getDataSource());
        String sql="SELECT g.id,g.price,g.good_name,p.picUrl FROM itlb_goods g LEFT JOIN\n" +
                "\t(SELECT good_id,SUM(buy_count)sales_count FROM itlb_order_details \n" +
                "\tGROUP BY good_id)s ON g.id=s.good_id LEFT JOIN itlb_goods_pic p \n" +
                "\tON g.id=p.goodsId WHERE g.status_line=1 ORDER BY s.sales_count";
        BeanListHandler<GoodsDTO> beanListHandler=new BeanListHandler<GoodsDTO>(GoodsDTO.class);
        List<GoodsDTO> list=queryRunner.query(sql,beanListHandler);
        return list;
    }


}
